/**
 * @description: excel导出
 */
import * as ExcelJS from "exceljs";
import { fileToBase64, base64ToBlob, base64ToBuffer, arrayToBase64, isBase64 } from "@/utils/util";
export class Excel {
  // 报表配置
  excelConfig: any;
  //用来判断是否是模板类型
  ift: Boolean;
  index: number;
  combinedJSON: any;
  constructor(data: any) {
    this.excelConfig = data;
    //用来判断是否是模板类型
    this.ift = true;
    // 用来判断获取到哪个层级的数据
    this.index = 1;
    this.combinedJSON = {
      dataSource: [
        {
          id: "1",
          b2: "父级数据1",
          b3: "测试",
          b4: "",
          b5: "全宗",
          b6: "2020-01-01",
          b7: "1"
        },
        {
          id: "2",
          b2: "父级数据2",
          b3: "测试123",
          b4: "data1_b4",
          b5: "33213123213",
          b6: "123213"
        }
      ],
      dataSource2: [
        {
          b1: "333333",
          id: "3",
          yh: "题名1",
          yc: "21",
          parentid: "1",
          b2: "测试1",
          b5: "3",
          b6: "2",
          b7: "2020-01"
        },
        {
          b1: "data1_1233333333333b1",
          id: "4",
          yh: "题名2",
          yc: "6",
          b2: "测试2",
          parentid: "1",
          b5: "2",
          b6: "123213",
          b7: "2020-01-01"
        },
        {
          b1: "data1_1233333333333b1",
          id: "5",
          yh: "题名3",
          yc: "1",
          b2: "测试3",
          parentid: "1",
          b5: "1",
          b6: "123213",
          b7: "2020-01-01"
        },
        {
          b1: "data1_1233333333333b1",
          id: "6",
          yh: "题名4",
          yc: "34",
          b2: "测试4",
          parentid: "1",
          b5: "6",
          b6: "123213",
          b7: "2020-01-01"
        },
        {
          b1: "data1_b4vceesadas",
          id: "7",
          yh: "题名5",
          yc: "44-60",
          b2: "测试5",
          parentid: "2",
          b5: "7",
          b6: "123213",
          b7: "2020-01-01"
        },
        {
          b1: "啊啊啊啊啊啊啊啊啊啊12345678901",
          id: "8",
          yh: "题名6",
          yc: "",
          b2: "测试6",
          parentid: "2",
          b5: "9",
          b6: "123213",
          b7: "2020-01-01"
        },
        {
          b1: "data1_1233333333333b1",
          id: "9",
          yh: "题名7",
          yc: null,
          b2: "测试7",
          parentid: "2",
          b5: "5",
          b6: "123213",
          b7: "2020-01-01"
        }
      ],
      dataSource3: [
        {
          b1: "data1_1233333333333b1",
          b2: "子数据1",
          parentid: "3",
          b4: "data1_b4",
          b5: "33213123213",
          b6: "123213"
        },
        {
          b1: "data1_1233333333333b1",
          b2: "子数据2",
          parentid: "4",
          b4: "data1_b4",
          b5: "33213123213",
          b6: "123213"
        },
        {
          b1: "data1_1233333333333b1",
          b2: "子数据3",
          parentid: "5",
          b4: "data1_b4",
          b5: "33213123213",
          b6: "123213"
        },
        {
          b1: "data1_1233333333333b1",
          b2: "子数据4",
          parentid: "6",
          b4: "data1_b4",
          b5: "33213123213",
          b6: "123213"
        },
        {
          b1: "data1_1233333333333b1",
          b2: "子数据5",
          parentid: "7",
          b4: "data1_b4",
          b5: "33213123213",
          b6: "123213"
        },
        {
          b1: "data1_1233333333333b1",
          b2: "子数据6",
          parentid: "8",
          b4: "data1_b4",
          b5: "33213123213",
          b6: "123213"
        },
        {
          b1: "data1_1233333333333b1",
          b2: "子数据7",
          parentid: "9",
          b4: "data1_b4",
          b5: "33213123213",
          b6: "123213"
        }
      ],
      //字典数据
      my_dict: {
        1: "John",
        2: "状态2",
        3: "状态3",
        4: "状态4",
        5: "状态5",
        6: "状态6",
        7: "状态7"
      },
      //用户组织架构
      userDB: [
        {
          b1: "data1_1233333333333b1",
          name: "名字1",
          id: "3",
          b4: "data1_b4",
          b5: "33213123213",
          b6: "123213"
        },
        {
          b1: "data1_1233333333333b1",
          name: "名字2",
          id: "4",
          b4: "data1_b4",
          b5: "33213123213",
          b6: "123213"
        },
        {
          b1: "data1_1233333333333b1",
          name: "名字3",
          id: "5",
          b4: "data1_b4",
          b5: "33213123213",
          b6: "123213"
        },
        {
          b1: "data1_1233333333333b1",
          name: "名字6",
          id: "6",
          b4: "data1_b4",
          b5: "33213123213",
          b6: "123213"
        },
        {
          b1: "data1_1233333333333b1",
          name: "名字7",
          id: "7",
          b4: "data1_b4",
          b5: "33213123213",
          b6: "123213"
        },
        {
          b1: "data1_1233333333333b1",
          name: "名字8",
          id: "8",
          b4: "data1_b4",
          b5: "33213123213",
          b6: "123213"
        },
        {
          b1: "data1_1233333333333b1",
          name: "名字9",
          id: "9",
          b4: "data1_b4",
          b5: "33213123213",
          b6: "123213"
        }
      ]
    };
  }
  download = async () => {
    console.log(JSON.parse(this.excelConfig.RM_Config));
    const config = JSON.parse(this.excelConfig.RM_Config);
    //设置最少有多少行
    const fewRow = config.Rows;
    //设置最少有多少列
    const fewColumns = config.Columns;
    //排序规则
    const sortRule = "id.asc";
    // const sortRule = "id.desc,b6.asc";
    //拿到指定层级的数据
    const dataSource = Object.values(this.combinedJSON)[this.index] as any[];
    const sortRulesArray = sortRule.split(",");
    // 使用Array.reduce来依次应用多个排序规则
    dataSource.sort((a: { [x: string]: any }, b: { [x: string]: any }) => {
      return sortRulesArray.reduce((result, rule) => {
        if (result !== 0) {
          return result;
        }
        const sortOrder = rule.endsWith(".desc") ? -1 : 1;
        const key = rule.replace(/\.asc|\.desc/, "");

        const aValue = a[key];
        const bValue = b[key];

        return aValue > bValue ? sortOrder : aValue < bValue ? -sortOrder : 0;
      }, 0);
    });
    // 将后端返回的模板数据作为模板
    const workbook = new ExcelJS.Workbook();
    const buffer: any = base64ToBuffer(this.excelConfig.RM_Entity);
    await workbook.xlsx.load(buffer);
    // 获取第一个工作表
    const worksheet: any = workbook.getWorksheet(1);

    // 用于记录找到的 'Item' 单元格的数组
    let foundItems: any[] = [];
    // 用于记录找到的 'Item' 单元格的数组（保留一份原始的数组）
    let foundItemsinit: any[] = [];
    // 获取要复制样式的源范围
    let sourceRangeStartRow: any = null; //起始行
    let sourceRangeEndRow: any = null; //结束行
    let sourceRangeStartColumn: any = null; //起始列
    let sourceRangeEndColumn: any = null; //结束列
    //拿到合并的单元格
    let merges = worksheet._merges;
    //记录item中有哪些单元格合并
    let merges2: any[] = [];
    // 遍历工作表的所有行
    worksheet.eachRow({ includeEmpty: true }, (row: any, rowNumber: any) => {
      // 遍历行的每个单元格
      row.eachCell({ includeEmpty: true }, (cell: any, colNumber: any) => {
        // 检查单元格是否有指定的名称
        if (cell.name && cell.name === "Item") {
          // 如果尚未记录起始行列，记录当前行列
          if (sourceRangeStartRow === null || sourceRangeStartColumn === null) {
            sourceRangeStartRow = rowNumber;
            sourceRangeStartColumn = colNumber;
          }
          // 每次找到 "Item" 单元格都更新结束行列
          if (rowNumber > sourceRangeEndRow) {
            sourceRangeEndRow = rowNumber;
          }
          if (colNumber > sourceRangeEndColumn) {
            sourceRangeEndColumn = colNumber;
          }
          if (cell.value == null || cell.value.toString() == "") {
            return;
          }
          if (cell.value.toString().includes("{") && cell.value.toString().includes("}")) {
            foundItems.push({
              row: rowNumber, //记录行
              column: colNumber, //记录列
              value: cell.value //记录值
            });
            foundItemsinit.push({
              row: rowNumber, //记录行
              column: colNumber, //记录列
              value: cell.value //记录值
            });
            //删除单元格中的约定
            worksheet.getCell(rowNumber, colNumber).value = null;
          }
        }
      });
    });
    console.log(foundItems);
    //记录约定
    const dataFields = [];
    for (let col = 0; col < foundItems.length; col++) {
      const cellValue = foundItems[col].value;
      dataFields.push(cellValue); // 将指定行的约定保存下来
    }
    const newdataRow = [...dataFields]; //  创建副本用来检索字段中是否包含约定中的字段.
    newdataRow.some((data, ue) => {
      if (data.includes(":")) {
        let parseData = this.parseDataField(data);
        newdataRow[ue] = parseData.b1;
      } else {
        newdataRow[ue] = newdataRow[ue].substring(1).slice(0, -1);
      }
    });
    console.log(newdataRow);

    //判断约定的字段是否存在数据源中
    const foundMissingProperty = dataSource.some((data: any, ue: any) => {
      const missingProperties = newdataRow.filter(property => !(property in data));
      if (missingProperties.length > 0) {
        alert(`以下字段不存在于数据源中：${missingProperties.join(", ")}`);
        return true; // 结束整个循环
      }
      return false; // 继续下一个元素的检查
    });
    //如果起始行和结束行相等则表示为表格类型
    if (sourceRangeStartRow == sourceRangeEndRow) {
      this.ift = false;
    }
    if (!foundMissingProperty) {
      let rowIndex = foundItems[0].row; //拿到起始行
      let rowIndexinit = foundItems[0].row; //记录起始行（保留一份初始化的行数）
      let newrowIndex = 0; //记录每条数据中跨的最大行
      //记录样式
      let rowStyle: any = {};
      //记录起始行整行的单元格
      let thirdRow;
      //记录行高
      let rowDimensions: any = {};
      //记录每条数据跨的最大行数
      let thirdRow2 = foundItems[0].row;
      //判断是否是表格类型
      let ifm: Boolean = false;
      // 遍历所有合并单元格
      Object.values(merges).forEach((merge: any) => {
        // 判断合并单元格是否在指定区域内
        if (
          merge.top >= sourceRangeStartRow &&
          merge.bottom <= sourceRangeEndRow &&
          merge.left >= sourceRangeStartColumn &&
          merge.right <= sourceRangeEndColumn
        ) {
          // 将在指定区域内的合并单元格添加到数组
          merges2.push(merge);
        }
      });
      // 创建一个新的合并单元格列表
      const newMerges2 = Object.values(merges2).map(merge => ({
        top: merge.top,
        left: merge.left,
        bottom: merge.bottom,
        right: merge.right
      }));
      //必须先合并再粘贴样式否则可能会导致样式的丢失
      for (let dataIndex = 1; dataIndex < dataSource.length; dataIndex++) {
        //判断是否需要换行
        let ifcelMerg = false;
        if (dataIndex % fewColumns == 0) {
          ifcelMerg = true;
        }

        // 创建一个新的合并单元格列表
        const newMerges = Object.values(merges2).map((merge, index) => {
          let newMerge = {};
          if (ifcelMerg) {
            //计算出需要换行的数量
            const xs = (dataIndex / fewColumns) * sourceRangeEndRow;
            newMerge = {
              top: newMerges2[index].top + xs,
              left: newMerges2[index].left,
              bottom: newMerges2[index].bottom + xs,
              right: newMerges2[index].right
            };
          } else {
            newMerge = {
              top: merge.top,
              left: merge.left + sourceRangeEndColumn,
              bottom: merge.bottom,
              right: merge.right + sourceRangeEndColumn
            };
          }
          return newMerge;
        });
        // 进行合并
        newMerges.forEach((merge: any) => {
          worksheet.mergeCells(merge.top, merge.left, merge.bottom, merge.right);
        });
        merges2 = newMerges;
      }
      //模板起始行
      let targetStartRow = sourceRangeStartRow;
      //计算出模板的大小
      let rowsToCopy = sourceRangeEndRow - sourceRangeStartRow + 1;
      // 循环数据源，包括初始的模板
      let xb = 1;
      for (let dataIndex = 1; dataIndex <= dataSource.length; dataIndex++) {
        //判断是否需要换行
        let ifcel = false;
        if (dataIndex % fewColumns == 0) {
          ifcel = true;
        }
        if (ifcel) {
          targetStartRow += rowsToCopy;
          xb = 0;
        }
        //记录起始列
        let startColumn;
        if (dataIndex != dataSource.length) {
          //算出数据的起始列
          startColumn = xb * sourceRangeEndColumn + 1;
        } else {
          //防止设置列数为1导致xb一直为0--导致负数报错
          //作用让他少循环一次（因为有初始模板的原因）
          if (xb != 0) {
            xb--;
          } else {
            targetStartRow -= rowsToCopy;
          }
          startColumn = xb * sourceRangeEndColumn + 1;
        }
        //循环行和列
        for (let i = 0; i < rowsToCopy; i++) {
          for (let j = sourceRangeStartColumn; j <= sourceRangeEndColumn; j++) {
            //拿到模板单元格
            const sourceCell = worksheet.getCell(sourceRangeStartRow + i, j);
            //拿到当前需要插入数据得单元格
            const targetCell = worksheet.getCell(targetStartRow + i, startColumn + j - sourceRangeStartColumn);
            if (ifm != true) {
              // 复制单元格数值
              targetCell.value = sourceCell.value;
            }
            // 复制单元格样式（边框、字体等）
            targetCell.style = {
              ...sourceCell.style // 将源单元格的样式复制到目标单元格
            };
            // 复制列宽度
            worksheet.getColumn(startColumn + j - sourceRangeStartColumn).width = worksheet.getColumn(j).width;
            // 复制行高度
            worksheet.getRow(targetStartRow + i).height = worksheet.getRow(sourceRangeStartRow + i).height;
          }
        }
        //记录fewColumns条数据中跨的最大行
        let newrowIndexfew = 0;
        //插入数据
        foundItems.forEach((t, col) => {
          //需要填入的数据
          let item;
          if (t.value.toString().includes("{") && t.value.toString().includes("}")) {
            if (t.value.includes(":")) {
              if (this.ift) {
                //如果是模板类型则把数据显示到他原有的位置上不做跨行处理
                rowIndex = t.row;
              }
              let result: any = this.hmif(
                t.column - 1,
                dataSource[dataIndex - 1],
                t.value,
                rowIndex,
                worksheet,
                sourceRangeEndRow,
                dataIndex - 1
              );

              if (result.type == "2") {
                //如果avg模式等于2就让起始行等于返回的行数
                rowIndex = result.rowIndex;
              }
              if (result.type != "0") {
                ifm = true;
              }
              if (newrowIndex < result.rowIndex) {
                //更新跨的最大行
                newrowIndex = result.rowIndex;
              }
            } else {
              //如果没有函数就直接拿到他的字段
              item = t.value.substring(1).slice(0, -1);
            }
          } else {
            //没有{}就插入他原有的值
            item = t.value;
          }
          if (item != null) {
            if (ifm) {
              //表格类型
              worksheet.getCell(rowIndex, t.column).value = dataSource[dataIndex - 1][item];
            } else {
              //模板类型
              worksheet.getCell(t.row, t.column).value = dataSource[dataIndex - 1][item];
            }
          }
        });
        if (newrowIndexfew < newrowIndex) {
          //更新跨的最大行
          newrowIndexfew = newrowIndex;
        }
        rowIndex = thirdRow2; // 下一行
        newrowIndex = 0;

        if (dataIndex % fewColumns == 0) {
          rowIndex = newrowIndexfew + 1;
          thirdRow2 = newrowIndexfew + 1;
          newrowIndexfew = 0;
        }
        xb++;

        //每次都把列数加上模板的总列数
        foundItems.forEach((t, p) => {
          //如果需要换行就加列
          if (ifcel) {
            t.row += rowsToCopy;
            t.column = foundItemsinit[p].column;
          } else {
            t.column += sourceRangeEndColumn;
          }
        });
      }
      if (ifm) {
        //如果是表格类型就复制样式 模板类型不需要因为已经复制过了
        rowStyle = {}; //记录样式
        //起始行
        thirdRow = worksheet.getRow(rowIndexinit);
        // 获取样式、字体
        thirdRow.eachCell({ includeEmpty: true }, (cell: any, colNumber: any) => {
          rowStyle[colNumber] = {
            font: cell.font,
            alignment: cell.alignment,
            border: cell.border,
            fill: cell.fill,
            numFmt: cell.numFmt,
            width: worksheet.getColumn(colNumber).width
          };
        });
        rowDimensions = {
          height: thirdRow.height
        };
      }
      if (!this.ift) {
        //拿到表头的最终行
        const bt = sourceRangeStartRow - 1;
        //所有行减去表头行为数据显示占用的所有行
        const rowcount = worksheet.rowCount - bt;
        let rowcountfor = worksheet.rowCount;
        if (fewRow > rowcount) {
          rowcountfor = fewRow;
        }
        //做缺行补齐处理
        for (let t = rowIndexinit; t < rowcountfor + 1; t++) {
          // 获取单元格的内容
          const cellContent = worksheet.getCell(t, fewColumns * sourceRangeEndColumn).value;

          if (!cellContent || cellContent === null || cellContent === undefined) {
            // 如果单元格内容为空 替换为空
            worksheet.getCell(t, fewColumns * sourceRangeEndColumn).value = " ";

            rowStyle = {}; //记录样式
            //起始行
            thirdRow = worksheet.getRow(rowIndexinit);
            // 获取样式、字体
            thirdRow.eachCell({ includeEmpty: true }, (cell: any, colNumber: any) => {
              rowStyle[colNumber] = {
                font: cell.font,
                alignment: cell.alignment,
                border: cell.border,
                fill: cell.fill,
                numFmt: cell.numFmt,
                width: worksheet.getColumn(colNumber).width
              };
            });
            const rowDimensions: any = {
              height: thirdRow.height
            };
          }
        }
      }
      if (Object.keys(rowStyle).length !== 0) {
        // 应用指定行的样式、列宽和行高到其他行
        for (let i = foundItems[0].row; i <= worksheet.rowCount; i++) {
          const row = worksheet.getRow(i);
          row.height = rowDimensions.height; // 设置行高
          row.eachCell({ includeEmpty: true }, (cell: any, colNumber: any) => {
            const style = rowStyle[colNumber];
            if (style) {
              cell.font = style.font;
              cell.alignment = style.alignment;
              cell.border = style.border;
              cell.fill = style.fill;
              cell.numFmt = style.numFmt;
              worksheet.getColumn(colNumber).width = style.width; // 设置列宽
            }
          });
        }
      }

      // 将填充数据后的工作簿转换为 Blob，并下载文件
      const filledBuffer = await workbook.xlsx.writeBuffer();
      this.downloadFile(filledBuffer, "filled_excel.xlsx", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    }
  };
  hmif(
    col: number,
    data: any,
    dataField: string,
    rowIndex: number,
    worksheet: any,
    sourceRangeEndRow: number,
    dataIndex: number
  ) {
    let type = "0";
    //拿到指定行
    let rowToFill = worksheet.getRow(rowIndex);
    //先判断表达式有没有冒号 没有就只是显示指定字段
    //解析约定得字段
    const leix: any = this.parseDataField(dataField);

    if (dataField.includes(":")) {
      //跨行
      if (leix.avg === "avg") {
        //如果函数等于avg
        return this.AvgOneORTwo(leix, col, data, dataField, rowIndex, worksheet, rowToFill);
      }
      //页次页号
      if (leix.avg === "pagenumber") {
        //拿到指定层级的数据
        const dataSource: any = Object.values(this.combinedJSON)[this.index];
        //拿到字段
        const dataFieldtow = leix.b1;
        //根据页号求页次
        if (dataFieldtow == "yh") {
          const yhValue: any = data.yh;
          // 检查是否为空
          if (yhValue == null || yhValue == "") {
            data.yh = 0;
          }
          if (dataIndex == 0) {
            //第一条
            rowToFill.getCell(col + 1).value = "1";
          }
          if (dataIndex == dataSource.length - 1) {
            //最后一条
            // 截取指定下标之前的数据
            const dataBeforeIndex = dataSource.slice(0, dataIndex);
            // 对 yh 字段进行累加
            const sumOfYh = dataBeforeIndex.reduce((total: number, item: { yh: string }) => {
              return total + (parseFloat(item.yh) || 0);
            }, 0);
            const PageCount = sumOfYh + parseInt(data[dataFieldtow], 10);
            rowToFill.getCell(col + 1).value = sumOfYh + 1 + "-" + PageCount;
          } else {
            // 截取指定下标之前的数据
            const dataBeforeIndex = dataSource.slice(0, dataIndex);
            // 对 yh 字段进行累加
            const sumOfYh = dataBeforeIndex.reduce((total: number, item: { yh: string }) => {
              return total + (parseFloat(item.yh) || 0);
            }, 0);
            rowToFill.getCell(col + 1).value = sumOfYh + 1;
          }
        }
        //根据页次求页号
        if (dataFieldtow == "yc") {
          const ycValue = data.yc;
          // 检查是否为空
          if (ycValue == null || ycValue == "") {
            data.yc = 0;
          }
          if (dataIndex == dataSource.length - 1) {
            // 使用 split 方法拆分字符串
            const parts = data.yc.split("-");
            const result = (parseFloat(parts[1]) || 0) - (parseFloat(parts[0]) || 0);
            rowToFill.getCell(col + 1).value = Math.max(result, 0);
          } else {
            if (dataIndex + 1 == dataSource.length - 1) {
              //倒数第二条
              const parts = data.yc;
              const pagenumb = (parseFloat(dataSource[dataIndex + 1].yc) || 0) - (parseFloat(parts) || 0);
              rowToFill.getCell(col + 1).value = Math.max(pagenumb, 0);
            } else {
              const pagenumb = (parseFloat(dataSource[dataIndex + 1].yc) || 0) - (parseFloat(data.yc) || 0);
              rowToFill.getCell(col + 1).value = Math.max(pagenumb, 0);
            }
          }
        }
        if (this.ift) {
          rowIndex += sourceRangeEndRow - 1;
        }
        return { rowIndex, type };
      }
      //组织架构
      if (leix.avg === "organi") {
        //拿到字段
        const dataFieldtow = leix.b1;
        //获取对应字段数据
        const dataarr = data[dataFieldtow];
        //拿到指定层级的数据
        const valueForKey: any = Object.values(this.combinedJSON)[leix.five - 1];
        //在组织结构中查找对应id的数据
        const foundData = valueForKey.find((item: { id: any }) => item.id === dataarr);

        rowToFill.getCell(col + 1).value = foundData[leix.one];
        if (this.ift) {
          rowIndex += sourceRangeEndRow - 1;
        }
        return { rowIndex, type };
      }
      //字典
      if (leix.avg === "dict") {
        const dataFieldtow = leix.b1;
        const dataarr = data[dataFieldtow];
        //拿到指定层级的数据
        const valueForKey: any = Object.values(this.combinedJSON)[leix.five - 1];

        rowToFill.getCell(col + 1).value = valueForKey[dataarr];
        if (this.ift) {
          rowIndex += sourceRangeEndRow - 1;
        }
        return { rowIndex, type };
      }
      //父级
      if (leix.avg === "parent") {
        const dataFieldtow = leix.b1;
        if (this.index > 0) {
          //拿到父级数据
          const parentData: any = Object.values(this.combinedJSON)[this.index - 1];
          //拿到父子级关联的字段
          const dataarr = data["parentid"];
          //查找父级数据
          const foundData = parentData.find((item: { id: any }) => item.id === dataarr);
          //显示指定字段内容
          rowToFill.getCell(col + 1).value = foundData[dataFieldtow];
        } else {
          rowToFill.getCell(col + 1).value = "父级为空";
        }
        if (this.ift) {
          rowIndex += sourceRangeEndRow - 1;
        }
        return { rowIndex, type };
      }
      //子级
      if (leix.avg === "child") {
        if (this.index != Object.values(this.combinedJSON).length) {
          const dataFieldtow = leix.b1;
          //需要显示第几条子级数据
          const childdb = leix.five;
          //拿到父子级关联的字段
          const dataarr = data["id"];
          //拿到子级数据
          const childData: any = Object.values(this.combinedJSON)[this.index + 1];
          //查找指定子级数据
          const foundData = childData.filter((item: { parentid: any }) => item.parentid === dataarr);
          if (foundData != null) {
            //显示指定字段内容
            //插入数据
            const secondFoundData = foundData.length >= childdb ? foundData[childdb - 1] : null;
            if (secondFoundData != null) {
              rowToFill.getCell(col + 1).value = secondFoundData[dataFieldtow];
            }
          }
        } else {
          rowToFill.getCell(col + 1).value = "子级为空";
        }
        if (this.ift) {
          rowIndex += sourceRangeEndRow - 1;
        }
        return { rowIndex, type };
      }
      //日期格式
      if (leix.avg === "date") {
        const dataFieldtow = leix.b1;
        const dataarr = data[dataFieldtow];
        const dateObject = new Date(dataarr);
        // 判断日期字符串的格式并处理
        if (isNaN(dateObject.getFullYear())) {
          // 无效的日期，保持原样
          rowToFill.getCell(col + 1).value = dataarr;
        } else {
          const year = dateObject.getFullYear().toString();
          const month = (dateObject.getMonth() + 1).toString().padStart(2, "0");
          const day = dateObject.getDate().toString().padStart(2, "0");

          // 根据传入的格式构建日期字符串
          switch (leix.five) {
            case "yyyy":
              rowToFill.getCell(col + 1).value = year;
              break;
            case "yyyy-mm":
              rowToFill.getCell(col + 1).value = `${year}-${month}`;
              break;
            case "yyyy-mm-dd":
              rowToFill.getCell(col + 1).value = `${year}-${month}-${day}`;
              break;
            default:
              rowToFill.getCell(col + 1).value = dataarr;
              break;
          }
        }
        if (this.ift) {
          rowIndex += sourceRangeEndRow - 1;
        }
        return { rowIndex, type };
      }
    } else {
      //显示指定字段得数据
      //截取表达式中的字段名称
      const dataFieldtow = dataField.substring(1, dataField.length - 1);
      let dataarr = data[dataFieldtow];

      rowToFill.getCell(col + 1).value = dataarr;
      return { rowIndex, type };
    }
  }
  totalRowsNeeded(cellValue: any, bytesPerParagraph: number) {
    const chineseRegex = /[\u4E00-\u9FA5]/; // 匹配中文字符的正则表达式
    let paragraphs = 0;
    let remaining = cellValue.length;

    while (remaining > 0) {
      let charsInParagraph = 0;
      let endIndex = 0;

      // 查找每个段落的结束位置
      while (endIndex < cellValue.length && charsInParagraph < bytesPerParagraph) {
        if (chineseRegex.test(cellValue[endIndex])) {
          if (charsInParagraph + 2 <= bytesPerParagraph) {
            charsInParagraph += 2; // 中文字符算两个字节
            endIndex++;
          } else {
            break;
          }
        } else {
          if (charsInParagraph + 1 <= bytesPerParagraph) {
            charsInParagraph++; // 英文字符算一个字节
            endIndex++;
          } else {
            break;
          }
        }
      }

      paragraphs++;
      remaining -= endIndex;
      cellValue = cellValue.substring(endIndex);
    }

    return paragraphs;
  }
  firstBytes(inputString: string, bytesPerParagraph: number) {
    let bytesCount = 0;
    let index = 0;

    // 遍历字符串，累计字节长度，直到达到指定字节或字符串末尾
    while (bytesCount < bytesPerParagraph && index < inputString.length) {
      const charCode = inputString.charCodeAt(index);
      bytesCount += charCode >= 0 && charCode <= 127 ? 1 : 2; // ASCII字符占一个字节，中文字符占两个字节
      index++;
    }

    // 如果最后一个字符是中文字符且超过指定字节，需要回退一个字符
    if (bytesCount > 5 && inputString.charCodeAt(index - 1) > 127) {
      index--;
    }

    // 使用 substring 方法获取前面的子字符串
    return inputString.substring(0, index);
  }
  remainingString(inputString: string, bytesPerParagraph: number) {
    let bytesCount = 0;
    let index = 0;

    // 遍历字符串，累计字节长度，直到达到指定字节数或字符串末尾
    while (bytesCount < bytesPerParagraph && index < inputString.length) {
      const charCode = inputString.charCodeAt(index);
      bytesCount += charCode >= 0 && charCode <= 127 ? 1 : 2; // ASCII字符占一个字节，中文字符占两个字节
      index++;
    }

    // 如果最后一个字符是中文字符且超过指定字节数，需要回退一个字符
    if (bytesCount > bytesPerParagraph && inputString.charCodeAt(index - 1) > 127) {
      index--;
    }

    // 使用 substring 方法获取除了前面指定字节数之后的剩余字符串
    const remainingString = inputString.substring(index);

    return remainingString;
  }
  AvgOneORTwo(
    leix:
      | { avg: any; b1: any; five: any; one: any }
      | { avg: any; b1: any; five: any; one?: undefined }
      | { avg?: undefined; b1?: undefined; five?: undefined; one?: undefined },
    col: number,
    data: { [x: string]: any },
    dataField: any,
    rowIndex: number,
    worksheet: { getRow: (arg0: any) => any; insertRow: (arg0: any) => void },
    rowToFill: { getCell: (arg0: any) => { (): any; new (): any; value: any } }
  ) {
    //拿到avg得模式（1或者2）
    let type = leix.one;
    //拿到字段
    const cellValue = data[leix.b1];

    const maxCharactersPerCell = leix.five; // 设置单元格最大字符数

    //大于最大字节就循环切割跨行插入
    if (cellValue.length > maxCharactersPerCell) {
      //拿到需要跨多少行
      const totalRowsNeeded = this.totalRowsNeeded(cellValue, maxCharactersPerCell);

      let remainingValue = cellValue;
      for (let i = 0; i < totalRowsNeeded; i++) {
        //截取指定字节
        const cellData = this.firstBytes(remainingValue, maxCharactersPerCell);
        //赋值
        rowToFill.getCell(col + 1).value = cellData;
        //拿到指定字节后面得字节
        remainingValue = this.remainingString(remainingValue, maxCharactersPerCell);

        if (remainingValue.length > 0) {
          rowIndex++;
          //作用补跨行导致的空单元格
          // const col2 = col + 1;
          // for (let j = 1; j <= Fieldstotal - col2; j++) {
          //   worksheet.getCell(rowIndex - 1, col2 + j).value = " ";
          // }
          if (!worksheet.getRow(rowIndex + 1)) {
            worksheet.insertRow(rowIndex + 1);
          }
          rowToFill = worksheet.getRow(rowIndex);
        }
      }
    } else {
      //否则直接显示
      rowToFill.getCell(col + 1).value = cellValue;
    }
    return { rowIndex, type };
  }
  parseDataField(dataField: string) {
    const colonCount = (dataField.match(/:/g) || []).length;
    let avg;
    let b1;
    let five;
    let one;

    if (colonCount > 0) {
      if (colonCount == 3) {
        // 有多个冒号的情况，例如 {avg:b2:5:1}
        const colonsIndexes = [];
        let index = dataField.indexOf(":");
        while (index !== -1) {
          colonsIndexes.push(index);
          index = dataField.indexOf(":", index + 1);
        }
        avg = dataField.substring(dataField.indexOf("{") + 1, colonsIndexes[0]);
        b1 = dataField.substring(colonsIndexes[0] + 1, colonsIndexes[1]);
        five = dataField.substring(colonsIndexes[1] + 1, colonsIndexes[2]);
        one = dataField.substring(colonsIndexes[2] + 1, dataField.lastIndexOf("}"));
        return { avg, b1, five, one };
      } else if (colonCount == 2) {
        const colonsIndexes = [];
        let index = dataField.indexOf(":");

        while (index !== -1) {
          colonsIndexes.push(index);
          index = dataField.indexOf(":", index + 1);
        }

        const avg = dataField.substring(dataField.indexOf("{") + 1, colonsIndexes[0]);

        const b1 = dataField.substring(colonsIndexes[0] + 1, colonsIndexes[1]);
        const five = dataField.substring(colonsIndexes[1] + 1, dataField.lastIndexOf("}"));
        return { avg, b1, five };
      } else if (colonCount == 1) {
        // 只有一个冒号的情况，例如 {dict:b2}
        const matchArray = dataField.match(/\{([^}]+):([^}]+)\}/);
        if (matchArray) {
          const [key, value] = matchArray.slice(1);
          avg = key;
          b1 = value;
          return { avg, b1, five, one };
        }
      }
    }
    // 无法解析，返回空对象或适当的默认值
    return {};
  }
  downloadFile(buffer: BlobPart, fileName: string, fileType: string) {
    const blob = new Blob([buffer], { type: fileType });
    const link = document.createElement("a");
    link.href = window.URL.createObjectURL(blob);
    link.download = fileName;
    link.click();
  }
}
